package org.lq.education.dao.impl;

import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.education.dao.AttendanceDao;
import org.lq.education.entity.AttendanceInfo;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 崔竞辉
 * @create 2020-10-13 18:26
 */
@Log4j
public class AttendanceDaoImpl implements AttendanceDao {

    /**
     * 添加学员考勤
     * @param attendance_info
     * @return
     */
    @Override
    public int save(AttendanceInfo attendance_info) {
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("数据访问层:学员考勤-save-start---");
        try {
          num = qr.update("insert into attendance_info(student_id,attendance_desc,attendance_state,attendance_time,attendance_remark)" +
                    "values(?,?,?,now(),?)"
                    ,attendance_info.getStudentId()
                    ,attendance_info.getAttendanceDesc()
                    ,attendance_info.getAttendanceState()
                    ,attendance_info.getAttendanceRemark());
        } catch (SQLException e) {
            log.info("数据访问层:学员考勤-save-"+num);
        }
        log.info("数据访问层:学员考勤-save-end---");
        return num;
    }

    /**
     * 修改考勤情况
     * @param attendance_info
     * @return
     */
    @Override
    public int update(AttendanceInfo attendance_info) {
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("数据访问层:学员考勤-update-start---");
        try {
            num = qr.update("update attendance_info set student_id=?,attendance_desc=?,attendance_state=?,attendance_time=?,attendance_remark=? where attendance_id=?"
                    ,attendance_info.getStudentId()
                    ,attendance_info.getAttendanceDesc()
                    ,attendance_info.getAttendanceState()
                    ,attendance_info.getAttendanceTime()
                    ,attendance_info.getAttendanceRemark()
                    ,attendance_info.getAttendanceId());
        } catch (SQLException e) {
            log.info("数据访问层:学员考勤-update-"+num);
        }
        log.info("数据访问层:学员考勤-update-end---");
        return num;
    }

    /**
     * 删除学员考勤数据
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("数据访问层:学员考勤-delete-start---");
        try {
            num = qr.update("delete from attendance_info where attendance_id=?",id);
            log.info("数据访问层:学员考勤-delete-"+num);
        } catch (SQLException e) {
        }
        log.info("数据访问层:学员考勤-delete-end---");
        return num;
    }

    /**
     * 根据学员ID查询
     * @param id
     * @return
     */
    @Override
    public AttendanceInfo getById(int id) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("数据访问层:学员考勤-getById-start---");
        AttendanceInfo attendanceInfo = null;
        try {
          attendanceInfo = qr.query("select * from attendanceinfo where attendanceId=?"
            ,new BeanHandler<AttendanceInfo>(AttendanceInfo.class));
        } catch (SQLException e) {
            log.info("数据访问层:学员考勤-getById-"+attendanceInfo);
        }
        log.info("数据访问层:学员考勤-getById-end---");
        return attendanceInfo;
    }

    /**
     * 查询总行数
     * @return
     */
    @Override
    public int getCount() {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("数据访问层:学员考勤-getCount-start---");
        int num = 0;
        try {
            num = qr.query("select count(1) from attendanceinfo"
            ,new ScalarHandler<Long>()).intValue();
        } catch (SQLException e) {
            log.info("数据访问层:学员考勤-getCount-"+num);
        }
        log.info("数据访问层:学员考勤-getCount-end---");
        return num;
    }

    /**
     * 分页查询
     * @param startIndex 每页开始位置
     * @param pageSize 每页显示的行数
     * @return
     */
    @Override
    public List<AttendanceInfo> pageList(int startIndex, int pageSize) {
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("数据访问层:学员考勤-pageList-start---");
        List<AttendanceInfo> list = new ArrayList<>();
        AttendanceInfo attendanceInfo = null;
        try {
            list = qr.query("select * from attendanceinfo limit ?,?"
            ,new BeanListHandler<AttendanceInfo>(AttendanceInfo.class)
            ,startIndex,pageSize);
            log.info("数据访问层:学员考勤-pageList--"+list);
        } catch (SQLException e) {
        }
        log.info("数据访问层:学员考勤-pageList-end---");
        return list;
    }

    /**
     * 查询总行数
     * @param values
     * @return
     */
    @Override
    public int getCount(String... values) {
        int num = 0;
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("数据访问层:学员考勤-带参getCount-start---");
        try {
            num = qr.query("select count(1) from attendanceinfo where attendanceState like ?"
                    ,new ScalarHandler<Long>(),"%"+values[0]+"%").intValue();
        } catch (SQLException e) {
            log.info("数据访问层:学员考勤-带参getCount-"+num);
        }
        log.info("数据访问层:学员考勤-带参getCount-end---");
        return num;
    }

    /**
     * 分页查询
     * @param startIndex 每页开始的位置
     * @param pageSize 每页显示的行数
     * @param value 查询的条件值
     * @return
     */
    @Override
    public List<AttendanceInfo> pageByValues(int startIndex, int pageSize, String... value) {
        List<AttendanceInfo> list = new ArrayList<>();
        QueryRunner qr = new QueryRunner(JDBCUtil.getDataSource());
        log.info("数据访问层:学员考勤-pageByValues-start---");
        try {
            list = qr.query("select * from attendanceinfo where value like ? limit ?,?"
                ,new BeanListHandler<AttendanceInfo>(AttendanceInfo.class)
                ,"%"+value[0]+"%",startIndex,pageSize);
        } catch (SQLException e) {
            log.info("数据访问层:学员考勤-pageByValues-"+list);
        }
        log.info("数据访问层:学员考勤-pageByValues-end---");
        return list;
    }
}
